﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Dapper;
using System.Linq;
using Gx.Model.Entity;//实体
using Gx.Common;//通用类

namespace Gx.Dal.Shared
{
    public partial class C_AreaInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddAreaInfo(C_AreaInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO C_AreaInfo");
            strSql.Append("(");
            strSql.Append("AreaName,");
            strSql.Append("Sort,");
            strSql.Append("ParentId");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@AreaName,");
            strSql.Append("@Sort,");
            strSql.Append("@ParentId");
            strSql.Append(")");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model) > 0;
            }
        }

        public bool EditAreaInfo(C_AreaInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE C_AreaInfo SET ");
            strSql.Append(" AreaName=@AreaName,");
            strSql.Append(" Sort=@Sort,");
            strSql.Append(" ParentId=@ParentId");
            strSql.Append(" WHERE AreaId=@AreaId");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model) > 0;
            }
        }

        public bool DelC_AreaInfo(int AreaId)
        {
            string strSql = "DELETE FROM C_AreaInfo WHERE AreaId=@AreaId";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@AreaId", SqlDbType.Int, 4) { Value = AreaId });
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), new { AreaId }) > 0;
            }
        }

        public List<C_AreaInfo> GetAreaInfoList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" AreaId");
            strSql.Append(",AreaName");
            strSql.Append(",Sort");
            strSql.Append(",ParentId");
            strSql.AppendFormat(" FROM C_AreaInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<C_AreaInfo> list = conn.Query<C_AreaInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        public List<C_AreaInfo> GetAreaInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            totalCount = GetAreaInfoCount(strWhere);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY AreaId DESC) AS ORDERBYID");
            strSql.Append(",AreaId");
            strSql.Append(",AreaName");
            strSql.Append(",Sort");
            strSql.Append(",ParentId");
            strSql.AppendFormat(" FROM C_AreaInfo WITH(NOLOCK) WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<C_AreaInfo> list = conn.Query<C_AreaInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        public int GetAreaInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM C_AreaInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                object obj = conn.ExecuteScalar(strSql.ToString()); return obj.IntParse();
            };
        }

        public C_AreaInfo GetAreaInfoModel(int AreaId)
        {
            string strSql = @"SELECT  A.AreaId ,
        A.AreaName ,
        A.Sort ,
        A.ParentId ,
        ISNULL(B.AreaName, '') AS ParentName
FROM    C_AreaInfo AS A WITH ( NOLOCK )
        LEFT JOIN dbo.C_AreaInfo AS B WITH ( NOLOCK ) ON A.ParentId = B.AreaId
WHERE   A.AreaId = @AreaId";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                C_AreaInfo model = conn.Query<C_AreaInfo>(strSql.ToString(), new { AreaId }).SingleOrDefault();
                return model;
            }
        }

        public int IsExists_AreaName(string AreaName, int AreaId)
        {
            string strSql = @"SELECT  COUNT(*)
FROM    dbo.C_AreaInfo WITH ( NOLOCK )
WHERE   AreaId <> @AreaId
        AND AreaName = @AreaName;";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                object obj = conn.ExecuteScalar(strSql.ToString(), new { AreaName, AreaId });
                return obj.IntParse();
            }
        }
    }
}
